package org.middlewaregr.services;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.ejb.Stateless;
import javax.jws.WebMethod;
import javax.jws.WebService;

import com.test.TheseslServceLocal;

@Stateless
@WebService
public class ThesesServce implements TheseslServceLocal {

	@WebMethod
	public int createThesesProposal(String title, String date,
			String consultant, String professor_id, String status_id,
			String description, String technologies, int execution_time) {
		Connection conn;
		try {
			conn = DbConnection.getDbConnection();
			PreparedStatement statement = conn
					.prepareStatement(
							"INSERT INTO `middlewaregr`.`theses_proposals`	(`title`,`description`,	`technologies`,	`execution_time`,	`date`,	`consultant`,	`professor_id`,	`status`)	VALUES	('"
									+ title
									+ "',	'"
									+ description
									+ "',	'"
									+ technologies
									+ "',	'"
									+ execution_time
									+ "',	'"
									+ date
									+ "',	'"
									+ consultant
									+ "',	'"
									+ professor_id
									+ "',	'"
									+ status_id + "'	);",
							Statement.RETURN_GENERATED_KEYS);

			statement.executeUpdate();
			ResultSet keys = statement.getGeneratedKeys();

			keys.next();
			int key = keys.getInt(1);
			keys.close();

			return key;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return -1;
	}

	@WebMethod
	public void createCommentOnProposal(int thesis_proposal_id, String date,
			String description, String teacher_id) {
		Connection conn;
		try {
			conn = DbConnection.getDbConnection();
			PreparedStatement statement = conn
					.prepareStatement("INSERT INTO `middlewaregr`.`proposal_comments` (`teacher_id`, `description`,`date`,`thesis_proposal_id`)VALUES('"
							+ teacher_id
							+ "','"
							+ description
							+ "', '"
							+ date
							+ "','" + thesis_proposal_id + "');");
			statement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@WebMethod
	public int createTheses(String graduation_date, String file_location,
			String department, String faculty, int theses_proposal_id) {
		Connection conn;
		try {
			conn = DbConnection.getDbConnection();
			PreparedStatement statement = conn
					.prepareStatement(
							"INSERT INTO `middlewaregr`.`theses`(`graduation_date`,	`file_location`,	`department`,	`faculty`,	`theses_proposal_id`)VALUES('"
									+ graduation_date
									+ " ',	'"
									+ file_location
									+ "',	'"
									+ department
									+ "',	'"
									+ faculty
									+ "',	'" + theses_proposal_id + "'	);",
							Statement.RETURN_GENERATED_KEYS);

			statement.executeUpdate();
			ResultSet keys = statement.getGeneratedKeys();

			keys.next();
			int key = keys.getInt(1);
			keys.close();

			return key;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return -1;
	}

	@WebMethod
	public void updateProposalStatus(int proposal_id, String status) {
		Connection conn;
		try {
			conn = DbConnection.getDbConnection();
			PreparedStatement statement = conn
					.prepareStatement("UPDATE `middlewaregr`.`theses_proposals`SET`status` = '"
							+ status
							+ "' WHERE	`proposal_id` = '"
							+ proposal_id + "'");
			statement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@WebMethod
	public int createThesesReview(int theses_id, String reviewer, String date,
			String relevance_to_topic, String description,
			String layout_and_style, String contributions,
			String recommendations, String conclusion, int assessment) {
		Connection conn;
		try {
			conn = DbConnection.getDbConnection();
			PreparedStatement statement = conn
					.prepareStatement(
							"INSERT INTO `middlewaregr`.`theses_reviews`	(`theses_id`,	`reviewer`,	`date`,	`relevance_to_topic`,	`description`,	`layout_and_style`, `contributions`,`recommendations`, 	`conclusion`,	`assessment`	)	VALUES	('"
									+ theses_id
									+ "',	'"
									+ reviewer
									+ "',	'"
									+ date
									+ "',	'"
									+ relevance_to_topic
									+ "',	'"
									+ description
									+ "',	'"
									+ layout_and_style
									+ "',	'"
									+ contributions
									+ "',	'"
									+ recommendations
									+ "',	'"
									+ conclusion + "',	'" + assessment + "'	);",
							Statement.RETURN_GENERATED_KEYS);

			statement.executeUpdate();
			ResultSet keys = statement.getGeneratedKeys();

			keys.next();
			int key = keys.getInt(1);
			keys.close();

			return key;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return -1;
	}
}
